package cn.zqy.demo3.day02.JdbcTemplate;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import cn.zqy.demo3.day02.Utils.JDBCUtils;
import org.junit.jupiter.api.Test;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class Demo02JdbcTemplateTest {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    @Test
    public void test_1() {
        String sql = "update emp set salary = 12000 where id = 1001";
        int num = template.update(sql);
        System.out.println(num);
    }

    // 2. 添加一条记录
    @Test
    public void test_2() {
        String sql = "insert into emp(id, ename, dept_id) values (?, ?, ?)";
        int num = template.update(sql, 2222, "麻麻", 10);
        System.out.println(num);
    }

    // 3.删除刚才添加的记录
    @Test
    public void test_3() {
        String sql = "delete from emp where id = 2222";
        int num = template.update(sql);
        System.out.println(num);
    }

    // 4.查询id为1001的记录，将其封装为Map集合
    // 【注意】：这个方法查询的结果集长度只能是1
    @Test
    public void test_4() {
        String sql = "select * from emp where id = 1001";
        Map<String, Object> stringObjectMap = template.queryForMap(sql);
        System.out.println(stringObjectMap);
    }

    // 5. 查询所有记录，将其封装为List
    @Test
    public void test_5() {
        String sql = "select * from emp";
        List<Map<String, Object>> maps = template.queryForList(sql);
        for (Map<String, Object> mp : maps) {
            System.out.println(mp);
        }
    }

    // 6. 查询所有记录，将其封装为Emp对象的List集合
    @Test
    public void test_6() {
        String sql = "select * from emp";
        List<Demo02Emp> list = template.query(sql, (resultSet, i) -> {
            int id = resultSet.getInt("id");
            String ename = resultSet.getString("ename");
            int job_id = resultSet.getInt("job_id");
            int mgr =  resultSet.getInt("mgr");
            Date joindate = resultSet.getDate("joindate");
            double salary = resultSet.getDouble("salary");
            double bonus = resultSet.getDouble("bonus");
            int dept_id  = resultSet.getInt("dept_id");

            Demo02Emp emp = new Demo02Emp();
            emp.setId(id);
            emp.setEname(ename);
            emp.setJob_id(job_id);
            emp.setMgr(mgr);
            emp.setJoindate(joindate);
            emp.setSalary(salary);
            emp.setBonus(bonus);
            emp.setDept_id(dept_id);

            return emp;
        });
        System.out.println(list);
    }


    // 6_1. 内置对象查询所有记录，将其封装为Emp对象的List集合
    @Test
    public void test_6_1() {
        String sql = "select * from emp";
        List<Demo02Emp> list = template.query(sql, new BeanPropertyRowMapper<Demo02Emp>(Demo02Emp.class));
        System.out.println(list);
    }

    // 7. 查询总记录数
    @Test
    public void test_7() {
        String sql = "select Count(id) from emp";
        long num = template.queryForObject(sql, Long.class);
        System.out.println(num);
    }
}
